/*
 * Copyright (c) 2011, 2024 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     Mike Norman - Nov.9, 2011 - 2.4: test use of Advanced JDBC types from
//                                      within a PL/SQL Package
package dbws.testing.advancedjdbcpackage;

//javase imports
import java.io.StringReader;
import org.w3c.dom.Document;

//java eXtension imports
import javax.wsdl.WSDLException;

//JUnit4 imports
import org.junit.AfterClass;
import org.junit.BeforeClass;
//import org.junit.Ignore;
import org.junit.Test;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

//EclipseLink imports
import org.eclipse.persistence.internal.xr.Invocation;
import org.eclipse.persistence.internal.xr.Operation;
import org.eclipse.persistence.oxm.XMLMarshaller;
import org.eclipse.persistence.oxm.XMLUnmarshaller;

//testing imports
import dbws.testing.DBWSTestSuite;

public class AdvancedJDBCPackageTestSuite extends DBWSTestSuite {

    static final String CREATE_REGION_TYPE =
            """
                    CREATE OR REPLACE TYPE DBWS_REGION AS OBJECT (
                    REG_ID NUMBER(5),
                    REG_NAME VARCHAR2(50)
                    )""";
    static final String CREATE_EMP_ADDRESS_TYPE =
            """
                    CREATE OR REPLACE TYPE DBWS_EMP_ADDRESS AS OBJECT (
                    STREET VARCHAR2(100),
                    SUBURB VARCHAR2(100),
                    ADDR_REGION DBWS_REGION,
                    POSTCODE INTEGER
                    )""";
    static final String CREATE_EMP_OBJECT_TYPE =
            """
                    CREATE OR REPLACE TYPE DBWS_EMP_OBJECT AS OBJECT (
                    EMPLOYEE_ID NUMBER(8),
                    ADDRESS DBWS_EMP_ADDRESS,
                    EMPLOYEE_NAME VARCHAR2(80),
                    DATE_OF_HIRE  DATE
                    )""";
    static final String CREATE_EMP_INFO_TYPE =
            """
                    CREATE OR REPLACE TYPE DBWS_EMP_INFO AS OBJECT (
                    ID NUMBER(5),
                    NAME VARCHAR2(50)
                    )""";
    static final String CREATE_EMP_INFO_ARRAY_TYPE =
        "CREATE OR REPLACE TYPE DBWS_EMP_INFO_ARRAY AS VARRAY(3) OF DBWS_EMP_INFO";
    static final String CREATE_ADVANCED_OBJECT_DEMO_PACKAGE =
            """
                    CREATE OR REPLACE PACKAGE DBWS_ADVANCED_OBJECT_DEMO AS
                    FUNCTION ECHOREGION(AREGION IN DBWS_REGION) RETURN DBWS_REGION;
                    FUNCTION ECHOEMPADDRESS(ANEMPADDRESS IN DBWS_EMP_ADDRESS) RETURN DBWS_EMP_ADDRESS;
                    FUNCTION ECHOEMPOBJECT(ANEMPOBJECT IN DBWS_EMP_OBJECT) RETURN DBWS_EMP_OBJECT;
                    FUNCTION BUILDEMPARRAY(NUM IN INTEGER) RETURN DBWS_EMP_INFO_ARRAY;
                    END DBWS_ADVANCED_OBJECT_DEMO;""";
    static final String CREATE_ADVANCED_OBJECT_DEMO_BODY =
            """
                    CREATE OR REPLACE PACKAGE BODY DBWS_ADVANCED_OBJECT_DEMO AS
                    FUNCTION ECHOREGION(AREGION IN DBWS_REGION) RETURN DBWS_REGION AS
                    BEGIN
                    RETURN AREGION;
                    END ECHOREGION;
                    FUNCTION ECHOEMPADDRESS(ANEMPADDRESS IN DBWS_EMP_ADDRESS) RETURN DBWS_EMP_ADDRESS AS
                    BEGIN
                    RETURN ANEMPADDRESS;
                    END ECHOEMPADDRESS;
                    FUNCTION ECHOEMPOBJECT(ANEMPOBJECT IN DBWS_EMP_OBJECT) RETURN DBWS_EMP_OBJECT AS
                    BEGIN
                    RETURN ANEMPOBJECT;
                    END ECHOEMPOBJECT;
                    FUNCTION BUILDEMPARRAY(NUM IN INTEGER) RETURN DBWS_EMP_INFO_ARRAY AS
                    L_DATA DBWS_EMP_INFO_ARRAY := DBWS_EMP_INFO_ARRAY();
                    BEGIN
                    FOR I IN 1 .. NUM LOOP
                    L_DATA.EXTEND;
                    L_DATA(I) := DBWS_EMP_INFO(I, 'entry ' || i);
                    END LOOP;
                    RETURN L_DATA;
                    END BUILDEMPARRAY;
                    END DBWS_ADVANCED_OBJECT_DEMO;""";
    static final String DROP_REGION_TYPE =
        "DROP TYPE DBWS_REGION";
    static final String DROP_EMP_ADDRESS_TYPE =
        "DROP TYPE DBWS_EMP_ADDRESS";
    static final String DROP_EMP_OBJECT_TYPE =
        "DROP TYPE DBWS_EMP_OBJECT";
    static final String DROP_EMP_INFO_ARRAY_TYPE =
        "DROP TYPE DBWS_EMP_INFO_ARRAY";
    static final String DROP_EMP_INFO_TYPE =
        "DROP TYPE DBWS_EMP_INFO";
    static final String DROP_ADVANCED_OBJECT_DEMO_PACKAGE =
        "DROP PACKAGE DBWS_ADVANCED_OBJECT_DEMO";

    static boolean ddlCreate = false;
    static boolean ddlDrop = false;
    static boolean ddlDebug = false;

    @BeforeClass
    public static void setUp() throws WSDLException {
        if (conn == null) {
            try {
                conn = buildConnection();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        String ddlCreateProp = System.getProperty(DATABASE_DDL_CREATE_KEY, DEFAULT_DATABASE_DDL_CREATE);
        if ("true".equalsIgnoreCase(ddlCreateProp)) {
            ddlCreate = true;
        }
        String ddlDropProp = System.getProperty(DATABASE_DDL_DROP_KEY, DEFAULT_DATABASE_DDL_DROP);
        if ("true".equalsIgnoreCase(ddlDropProp)) {
            ddlDrop = true;
        }
        String ddlDebugProp = System.getProperty(DATABASE_DDL_DEBUG_KEY, DEFAULT_DATABASE_DDL_DEBUG);
        if ("true".equalsIgnoreCase(ddlDebugProp)) {
            ddlDebug = true;
        }
        if (ddlCreate) {
            runDdl(conn, CREATE_REGION_TYPE, ddlDebug);
            runDdl(conn, CREATE_EMP_ADDRESS_TYPE, ddlDebug);
            runDdl(conn, CREATE_EMP_OBJECT_TYPE, ddlDebug);
            runDdl(conn, CREATE_EMP_INFO_TYPE, ddlDebug);
            runDdl(conn, CREATE_EMP_INFO_ARRAY_TYPE, ddlDebug);
            runDdl(conn, CREATE_ADVANCED_OBJECT_DEMO_PACKAGE, ddlDebug);
            runDdl(conn, CREATE_ADVANCED_OBJECT_DEMO_BODY, ddlDebug);
        }
        DBWS_BUILDER_XML_USERNAME =
            "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
            "<dbws-builder xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
              "<properties>" +
                  "<property name=\"projectName\">advancedjdbcpackage</property>" +
                  "<property name=\"logLevel\">off</property>" +
                  "<property name=\"username\">";
          DBWS_BUILDER_XML_PASSWORD =
                  "</property><property name=\"password\">";
          DBWS_BUILDER_XML_URL =
                  "</property><property name=\"url\">";
          DBWS_BUILDER_XML_DRIVER =
                  "</property><property name=\"driver\">";
          DBWS_BUILDER_XML_PLATFORM =
                  "</property><property name=\"platformClassname\">";
          DBWS_BUILDER_XML_MAIN =
                  "</property>" +
              "</properties>" +
              "<procedure " +
                  "name=\"echoRegion\" " +
                  "catalogPattern=\"DBWS_ADVANCED_OBJECT_DEMO\" " +
                  "procedurePattern=\"ECHOREGION\" " +
                  "isAdvancedJDBC=\"true\" " +
                  "returnType=\"dbws_regionType\" " +
              "/>" +
              "<procedure " +
                  "name=\"echoEmpAddress\" " +
                  "catalogPattern=\"DBWS_ADVANCED_OBJECT_DEMO\" " +
                  "procedurePattern=\"ECHOEMPADDRESS\" " +
                  "isAdvancedJDBC=\"true\" " +
                  "returnType=\"dbws_emp_addressType\" " +
              "/>" +
              "<procedure " +
                  "name=\"echoEmpObject\" " +
                  "catalogPattern=\"DBWS_ADVANCED_OBJECT_DEMO\" " +
                  "procedurePattern=\"ECHOEMPOBJECT\" " +
                  "isAdvancedJDBC=\"true\" " +
                  "returnType=\"dbws_emp_objectType\" " +
                  "/>" +
              "<procedure " +
                  "name=\"buildEmpArray\" " +
                  "catalogPattern=\"DBWS_ADVANCED_OBJECT_DEMO\" " +
                  "procedurePattern=\"BUILDEMPARRAY\" " +
                  "isAdvancedJDBC=\"true\" " +
                  "returnType=\"dbws_emp_info_arrayType\" " +
              "/>" +
            "</dbws-builder>";
          builder = null;
          DBWSTestSuite.setUp(".");
    }

    @AfterClass
    public static void tearDown() {
        if (ddlDrop) {
            runDdl(conn, DROP_ADVANCED_OBJECT_DEMO_PACKAGE, ddlDebug);
            runDdl(conn, DROP_EMP_INFO_ARRAY_TYPE, ddlDebug);
            runDdl(conn, DROP_EMP_INFO_TYPE, ddlDebug);
            runDdl(conn, DROP_EMP_OBJECT_TYPE, ddlDebug);
            runDdl(conn, DROP_EMP_ADDRESS_TYPE, ddlDebug);
            runDdl(conn, DROP_REGION_TYPE, ddlDebug);
        }
    }

    @Test
    public void struct1LevelDeep() {
        XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller();
        Object region = unmarshaller.unmarshal(new StringReader(REGION_XML));
        Invocation invocation = new Invocation("echoRegion");
        invocation.setParameter("AREGION", region);
        Operation op = xrService.getOperation(invocation.getName());
        Object result = op.invoke(xrService, invocation);
        assertNotNull("result is null", result);
        XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller();
        Document doc = xmlPlatform.createDocument();
        marshaller.marshal(result, doc);
        Document controlDoc = xmlParser.parse(new StringReader(REGION_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    static final String REGION_XML =
        REGULAR_XML_HEADER +
        "<dbws_regionType xmlns=\"urn:advancedjdbcpackage\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
        "<reg_id>5</reg_id>" +
        "<reg_name>this is a test</reg_name>" +
        "</dbws_regionType>";

    @Test
    public void struct2LevelDeep() {
        XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller();
        Object empAddress = unmarshaller.unmarshal(new StringReader(EMP_ADDRESS_XML));
        Invocation invocation = new Invocation("echoEmpAddress");
        invocation.setParameter("ANEMPADDRESS", empAddress);
        Operation op = xrService.getOperation(invocation.getName());
        Object result = op.invoke(xrService, invocation);
        assertNotNull("result is null", result);
        XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller();
        Document doc = xmlPlatform.createDocument();
        marshaller.marshal(result, doc);
        Document controlDoc = xmlParser.parse(new StringReader(EMP_ADDRESS_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    static final String EMP_ADDRESS_XML =
        REGULAR_XML_HEADER +
        "<dbws_emp_addressType xmlns=\"urn:advancedjdbcpackage\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
           "<street>20 Pinetrail Cres.</street>" +
           "<suburb>Centrepointe</suburb>" +
           "<addr_region>" +
              "<reg_id>5</reg_id>" +
              "<reg_name>this is a test</reg_name>" +
           "</addr_region>" +
           "<postcode>12</postcode>" +
        "</dbws_emp_addressType>";

    @Test
    public void struct3LevelDeep() {
        XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller();
        Object empObject = unmarshaller.unmarshal(new StringReader(EMP_OBJECT_XML));
        Invocation invocation = new Invocation("echoEmpObject");
        invocation.setParameter("ANEMPOBJECT", empObject);
        Operation op = xrService.getOperation(invocation.getName());
        Object result = op.invoke(xrService, invocation);
        assertNotNull("result is null", result);
        XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller();
        Document doc = xmlPlatform.createDocument();
        marshaller.marshal(result, doc);
        Document controlDoc = xmlParser.parse(new StringReader(EMP_OBJECT_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) +
            "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    static final String EMP_OBJECT_XML =
        REGULAR_XML_HEADER +
        "<dbws_emp_objectType xmlns=\"urn:advancedjdbcpackage\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
           "<employee_id>55</employee_id>" +
           "<address>" +
              "<street>20 Pinetrail Cres.</street>" +
              "<suburb>Centrepointe</suburb>" +
              "<addr_region>" +
                 "<reg_id>5</reg_id>" +
                 "<reg_name>this is a test</reg_name>" +
              "</addr_region>" +
              "<postcode>12</postcode>" +
           "</address>" +
           "<employee_name>Mike Norman</employee_name>" +
           "<date_of_hire>2011-11-11</date_of_hire>" +
        "</dbws_emp_objectType>";

    @Test
    public void buildEmpArray() {
        Invocation invocation = new Invocation("buildEmpArray");
        invocation.setParameter("NUM", 3);
        Operation op = xrService.getOperation(invocation.getName());
        Object result = op.invoke(xrService, invocation);
        assertNotNull("result is null", result);
        XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller();
        Document doc = xmlPlatform.createDocument();
        marshaller.marshal(result, doc);
        Document controlDoc = xmlParser.parse(new StringReader(EMP_INFO_ARRAY_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) +
            "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    static final String EMP_INFO_ARRAY_XML =
        REGULAR_XML_HEADER +
        "<dbws_emp_info_arrayType xmlns=\"urn:advancedjdbcpackage\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
            "<item>" +
                "<id>1</id>" +
                "<name>entry 1</name>" +
            "</item>" +
            "<item>" +
                "<id>2</id>" +
                "<name>entry 2</name>" +
            "</item>" +
            "<item>" +
                "<id>3</id>" +
                "<name>entry 3</name>" +
            "</item>" +
         "</dbws_emp_info_arrayType>";
}
